package com.party.course.util;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;

/**
 * 新建mysql数据库表工具类
 * 将xlsx文件中的表结构生成sql语句
 *
 * @author songkaiyue
 * @date 2025-05-06 14:56:03
 */
public class ExcelToSqlUtil {
    public static void main(String[] args) {
//        String excelFilePath = "C:\\Users\\Administrator\\Desktop\\testTable.xlsx"; // Excel 文件路径
        String excelFilePath = "E:\\workSpace\\bykj\\课程小程序\\需求\\省市区编码20250314对客版.xlsx"; // Excel 文件路径
        String outputFilePath = "C:\\Users\\17928\\Desktop\\testTable.sql"; // 输出文本文件路径

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis);
             FileWriter writer = new FileWriter(outputFilePath)) {
            Sheet sheet = workbook.getSheetAt(0);
//            //省编码
//            String proCode = sheet.getRow(1).getCell(1).getStringCellValue();
//            //省名称
//            String proName = sheet.getRow(1).getCell(2).getStringCellValue();
//            //市编码
//            String cityCode = sheet.getRow(1).getCell(3).getStringCellValue();
//            //市名称
//            String cityName = sheet.getRow(1).getCell(4).getStringCellValue();
//            //区编码
//            String areaCode = sheet.getRow(1).getCell(5).getStringCellValue();
//            //区名称
//            String areaName = sheet.getRow(1).getCell(6).getStringCellValue();

            // 遍历 Excel 行
            for (Row row : sheet) {
                // 跳过表头
                if (row.getRowNum() == 0) continue;
                //省编码
                double proCode = row.getCell(0).getNumericCellValue();
                //省名称
                String proName = row.getCell(1).getStringCellValue();
                //市编码
                double cityCode = row.getCell(2).getNumericCellValue();
                //市名称
                String cityName = row.getCell(3).getStringCellValue();
                //区编码
                double areaCode = row.getCell(4).getNumericCellValue();
                //区名称
                String areaName = row.getCell(5).getStringCellValue();
               if (row.getRowNum() == 1){
                   System.out.println("省编码,省名称,市编码,市名称,区编码,区名称");
                   System.out.println(proCode + "," + proName + "," + cityCode + "," + cityName + "," + areaCode + "," + areaName);
               }
            }

        } catch (IOException e) {
            throw new RuntimeException("Error processing the Excel file", e);
        }
    }
}
